package com.nullpointer.xchatfinal.database;

import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.nullpointer.xchatfinal.object.ChatType;
import com.nullpointer.xchatfinal.object.DetailedMessage;
import com.nullpointer.xchatfinal.object.GroupChat;
import com.nullpointer.xchatfinal.object.HistoryMessage;
import com.nullpointer.xchatfinal.object.MessageContent;
import com.nullpointer.xchatfinal.object.MessageState;
import com.nullpointer.xchatfinal.object.MessageContent.MsgType;
import com.nullpointer.xchatfinal.utils.KiraGameLog;

public class MessagesDataManager extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "XChat";
	private static final int DATABASE_VERSION = 2;// update new vs if you edit
													// colunm data

	private static MessagesDataManager messagesDataManager;
	private SQLiteDatabase database;

	// TABLE NAME
	private static final String TABLE_MESSAGES = "Messages";
	private static final String TABLE_HISTORY = "History";

	// MESSAGE COLUMN
	public static final String COLUMN_MESSAGE_ID = "messageId";
	public static final String COLUMN_MESSAGE_CONTENT = "messageContent";
	public static final String COLUMN_MESSAGE_CREATED_AT = "createdAt";
	public static final String COLUMN_MESSAGE_TARGET_ID = "targetId";
	public static final String COLUMN_MESSAGE_TARGET_NAME = "targetName";
	public static final String COLUMN_MESSAGE_TARGET_AVATAR_ID = "targetAvatarID";

	// HISTORY COLUMN
	public static final String COLUMN_HISTORY_LAST_MESSAGE_CONTENT = "lastMessageContent";
	public static final String COLUMN_HISTORY_CREATED_AT = "createdAt";
	public static final String COLUMN_HISTORY_TARGET_ID = "targetId";
	public static final String COLUMN_HISTORY_TARGET_NAME = "targetName";
	public static final String COLUMN_HISTORY_TARGET_AVATAR_ID = "targetAvatarID";
	public static final String COLUMN_HISTORY_UNREAD_NUM = "unreadNum";
	public static final String COLUMN_HISTORY_CHAT_TYPE = "chatType";
	public static final String COLUMN_HISTORY_IS_JOINED_GROUP = "isJoined";

	private static final String DATABASE_MESSAGE_CREATE = "CREATE TABLE "
			+ TABLE_MESSAGES + "(" + MessagesDataManager.COLUMN_MESSAGE_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
			+ MessagesDataManager.COLUMN_MESSAGE_CREATED_AT + " INTEGER, "
			+ MessagesDataManager.COLUMN_MESSAGE_TARGET_ID + " TEXT NOT NULL, "
			+ MessagesDataManager.COLUMN_MESSAGE_TARGET_AVATAR_ID + " TEXT, "
			+ MessagesDataManager.COLUMN_MESSAGE_TARGET_NAME
			+ " TEXT NOT NULL, " + MessagesDataManager.COLUMN_MESSAGE_CONTENT
			+ " TEXT NOT NULL);";

	private static final String DATABASE_HISTORY_CREATE = "CREATE TABLE "
			+ TABLE_HISTORY + "("
			+ MessagesDataManager.COLUMN_HISTORY_TARGET_ID
			+ " TEXT PRIMARY KEY NOT NULL, "
			+ MessagesDataManager.COLUMN_HISTORY_TARGET_AVATAR_ID + " TEXT, "
			+ MessagesDataManager.COLUMN_HISTORY_TARGET_NAME
			+ " TEXT NOT NULL, "
			+ MessagesDataManager.COLUMN_HISTORY_CREATED_AT + " INTEGER, "
			+ MessagesDataManager.COLUMN_HISTORY_UNREAD_NUM
			+ " INTEGER NOT NULL, "
			+ MessagesDataManager.COLUMN_HISTORY_CHAT_TYPE
			+ " INTEGER NOT NULL, "
			+ MessagesDataManager.COLUMN_HISTORY_IS_JOINED_GROUP
			+ " INTEGER NOT NULL, "
			+ MessagesDataManager.COLUMN_HISTORY_LAST_MESSAGE_CONTENT
			+ " TEXT NOT NULL);";

	public MessagesDataManager(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_MESSAGE_CREATE);
		db.execSQL(DATABASE_HISTORY_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_MESSAGES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISTORY);
		onCreate(db);
	}

	public static MessagesDataManager getInstance(Context context) {
		if (messagesDataManager == null) {
			messagesDataManager = new MessagesDataManager(context);
		}
		return messagesDataManager;
	}

	private void openDB() {
		if (database != null && database.isOpen()) {
			return;
		}
		database = this.getWritableDatabase();
	}

	public void closeDB() {
		if (database != null && database.isOpen()) {
			database.close();
		}
	}

	// MESSAGES
	public List<MessageContent> getMessages(String targetId, int page, int size) {
		List<MessageContent> results = new ArrayList<MessageContent>();
		try {
			openDB();
			String orderBy = COLUMN_MESSAGE_CREATED_AT + " DESC";
			String limit = null;
			if (size > 0) {
				limit = String.valueOf(page * size) + ","
						+ String.valueOf(size);
			}
			Cursor cursor = database.query(TABLE_MESSAGES, new String[] {
					COLUMN_MESSAGE_TARGET_ID, COLUMN_MESSAGE_CONTENT,
					COLUMN_MESSAGE_TARGET_NAME, COLUMN_MESSAGE_ID },
					COLUMN_MESSAGE_TARGET_ID + "=?", new String[] { targetId },
					null, null, orderBy, limit);

			if (cursor.moveToFirst()) {
				do {
					MessageContent item = convertCursorToMessageContent(cursor);
					if (item != null) {
						results.add(item);
					}
				} while (cursor.moveToNext());
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		Collections.reverse(results);
		return results;
	}

	private void insertDetailedMessage(DetailedMessage detailedMessage) {
		try {
			openDB();
			ContentValues values = getDetailedMessageValues(detailedMessage);
			database.insert(TABLE_MESSAGES, null, values);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private ContentValues getDetailedMessageValues(
			DetailedMessage detailedMessage) {
		ContentValues result = new ContentValues();
		long createAt = detailedMessage.getCreateAt();
		if (createAt == 0) {
			createAt = new Date().getTime();
		}
		if (detailedMessage.getMessageContent().getMessageID() != null) {
			result.put(COLUMN_MESSAGE_ID, detailedMessage.getMessageContent()
					.getMessageID());
		}
		result.put(COLUMN_MESSAGE_CREATED_AT, createAt);
		result.put(COLUMN_MESSAGE_CONTENT, detailedMessage.getMessageContent()
				.toJson());
		result.put(COLUMN_MESSAGE_TARGET_ID, detailedMessage.getTargetID());
		result.put(COLUMN_MESSAGE_TARGET_AVATAR_ID, "");
		result.put(COLUMN_MESSAGE_TARGET_NAME, detailedMessage.getTargetName());
		return result;
	}

	private MessageContent convertCursorToMessageContent(Cursor cursor) {
		try {
			MessageContent messageContent = MessageContent
					.fromJsonToMessageContent(cursor.getString(cursor
							.getColumnIndex(COLUMN_MESSAGE_CONTENT)));
			return messageContent;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<HistoryMessage> getAllHistoryMessages(int page, int size) {
		List<HistoryMessage> results = new ArrayList<HistoryMessage>();
		try {
			openDB();
			String orderBy = COLUMN_HISTORY_CREATED_AT + " DESC";
			String limit = null;
			if (size > 0) {
				limit = String.valueOf(page * size) + ","
						+ String.valueOf(size);
			}
			Cursor cursor = database.query(TABLE_HISTORY, new String[] {
					COLUMN_HISTORY_TARGET_ID, COLUMN_HISTORY_TARGET_NAME,
					COLUMN_HISTORY_LAST_MESSAGE_CONTENT,
					COLUMN_HISTORY_UNREAD_NUM, COLUMN_HISTORY_CHAT_TYPE,
					COLUMN_HISTORY_IS_JOINED_GROUP }, null, null, null, null,
					orderBy, limit);
			if (cursor.moveToFirst()) {
				do {
					HistoryMessage item = convertCursorToHistoryMessage(cursor);
					if (item != null) {
						results.add(item);
					}
				} while (cursor.moveToNext());
			}
			cursor.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return results;
	}

	private HistoryMessage convertCursorToHistoryMessage(Cursor cursor) {
		HistoryMessage historyMessage = new HistoryMessage();
		try {
			MessageContent messageContent = MessageContent
					.fromJsonToMessageContent(cursor.getString(cursor
							.getColumnIndex(COLUMN_HISTORY_LAST_MESSAGE_CONTENT)));
			if (messageContent == null) {
				return null;
			}
			DetailedMessage detailedMessage = new DetailedMessage();
			detailedMessage.setMessageContent(messageContent);
			detailedMessage.setTargetID(cursor.getString(cursor
					.getColumnIndex(COLUMN_HISTORY_TARGET_ID)));
			detailedMessage.setTargetName(cursor.getString(cursor
					.getColumnIndex(COLUMN_HISTORY_TARGET_NAME)));
			historyMessage.setUnreadNum(cursor.getInt(cursor
					.getColumnIndex(COLUMN_HISTORY_UNREAD_NUM)));
			historyMessage.setDetailedMessage(detailedMessage);
			historyMessage.setChatType(ChatType.values()[cursor.getInt(cursor
					.getColumnIndex(COLUMN_HISTORY_CHAT_TYPE))]);
			int isJoinedGroup = cursor.getInt(cursor
					.getColumnIndex(COLUMN_HISTORY_IS_JOINED_GROUP));
			historyMessage.setJoinedRoom((isJoinedGroup == 1));
			return historyMessage;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	private ContentValues getHistoryMessageContentValues(
			HistoryMessage historyMessage) {
		DetailedMessage detailedMessage = historyMessage.getDetailedMessage();
		ContentValues result = new ContentValues();
		result.put(COLUMN_HISTORY_UNREAD_NUM, historyMessage.getUnreadNum());
		result.put(COLUMN_HISTORY_CREATED_AT, new Date().getTime());
		result.put(COLUMN_HISTORY_LAST_MESSAGE_CONTENT, detailedMessage
				.getMessageContent().toJson());
		result.put(COLUMN_HISTORY_TARGET_ID, detailedMessage.getTargetID());
		result.put(COLUMN_HISTORY_TARGET_NAME, detailedMessage.getTargetName());
		result.put(COLUMN_HISTORY_CHAT_TYPE, historyMessage.getChatType()
				.ordinal());
		result.put(COLUMN_HISTORY_TARGET_AVATAR_ID, "");
		boolean isJoinedGroup = historyMessage.isJoinedRoom();
		result.put(COLUMN_HISTORY_IS_JOINED_GROUP, isJoinedGroup ? 1 : 0);

		return result;
	}

	public void setGroupNotJoined(String targetId) {
		try {
			openDB();
			ContentValues result = new ContentValues();
			result.put(COLUMN_HISTORY_IS_JOINED_GROUP, 0);
			database.update(TABLE_HISTORY, result, COLUMN_HISTORY_TARGET_ID
					+ " = ?", new String[] { targetId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void resetHistoryMessageUnreadNum(String targetId) {
		try {
			openDB();
			ContentValues result = new ContentValues();
			result.put(COLUMN_HISTORY_UNREAD_NUM, 0);
			database.update(TABLE_HISTORY, result, COLUMN_HISTORY_TARGET_ID
					+ " = ?", new String[] { targetId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private void addToListHistoryMessage(HistoryMessage historyMessage) {
		try {
			DetailedMessage detailedMessage = historyMessage
					.getDetailedMessage();
			openDB();
			Cursor cursor = database.query(TABLE_HISTORY, new String[] {
					COLUMN_HISTORY_TARGET_ID, COLUMN_HISTORY_UNREAD_NUM },
					COLUMN_HISTORY_TARGET_ID + "=?",
					new String[] { detailedMessage.getTargetID() }, null, null,
					null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				int unreadNum = cursor.getInt(cursor
						.getColumnIndex(COLUMN_HISTORY_UNREAD_NUM));
				if (historyMessage.getUnreadNum() == 0) {
					// reset
					unreadNum = 0;
				} else {
					unreadNum++;
				}
				cursor.close();
				ContentValues result = new ContentValues();
				result.put(COLUMN_HISTORY_LAST_MESSAGE_CONTENT, detailedMessage
						.getMessageContent().toJson());
				result.put(COLUMN_HISTORY_UNREAD_NUM, unreadNum);
				result.put(COLUMN_HISTORY_CREATED_AT, new Date().getTime());
				result.put(COLUMN_HISTORY_TARGET_NAME,
						detailedMessage.getTargetName());
				result.put(COLUMN_HISTORY_TARGET_AVATAR_ID, "");
				database.update(TABLE_HISTORY, result, COLUMN_HISTORY_TARGET_ID
						+ " = ?",
						new String[] { detailedMessage.getTargetID() });
			} else {
				database.insert(TABLE_HISTORY, null,
						getHistoryMessageContentValues(historyMessage));
				KiraGameLog.d("add new history message to database");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public int getTotalMessageNumUnread() {
		int messageNum = 0;
		try {
			openDB();
			Cursor cursor = database.query(TABLE_HISTORY,
					new String[] { COLUMN_HISTORY_UNREAD_NUM }, null, null,
					null, null, null, null);
			if (cursor.moveToFirst()) {
				do {
					messageNum = messageNum
							+ cursor.getInt(cursor
									.getColumnIndex(COLUMN_HISTORY_UNREAD_NUM));
				} while (cursor.moveToNext());
			}
			cursor.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return messageNum;
	}

	public void saveMessageDatabase(HistoryMessage historyMessage) {
		addToListHistoryMessage(historyMessage);
		insertDetailedMessage(historyMessage.getDetailedMessage());
	}

	public void deleteMessageAndHistoryByTargetID(String targetId) {
		try {
			openDB();
			database.delete(TABLE_HISTORY, COLUMN_HISTORY_TARGET_ID + " = ?",
					new String[] { targetId });
			database.delete(TABLE_MESSAGES, COLUMN_MESSAGE_TARGET_ID + " = ?",
					new String[] { targetId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteMessagesByTargetID(String targetId) {
		try {
			openDB();
			database.delete(TABLE_MESSAGES, COLUMN_MESSAGE_TARGET_ID + " = ?",
					new String[] { targetId });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public List<GroupChat> getAllJoinedGroup() {
		List<GroupChat> groupChats = new ArrayList<GroupChat>();
		try {
			openDB();
			Cursor cursor = database
					.query(TABLE_HISTORY, new String[] {
							COLUMN_HISTORY_TARGET_ID,
							COLUMN_HISTORY_TARGET_NAME },
							COLUMN_HISTORY_CHAT_TYPE + "=? " + "AND "
									+ COLUMN_HISTORY_IS_JOINED_GROUP + "=1",
							new String[] { String.valueOf(ChatType.CHAT_GROUP
									.ordinal()) }, null, null, null, null);
			if (cursor.moveToFirst()) {
				do {
					GroupChat groupChat = new GroupChat();
					groupChat.setGroupJID(cursor.getString(cursor
							.getColumnIndex(COLUMN_HISTORY_TARGET_ID)));
					groupChat.setName(cursor.getString(cursor
							.getColumnIndex(COLUMN_HISTORY_TARGET_NAME)));
					groupChats.add(groupChat);
				} while (cursor.moveToNext());
			}
			cursor.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return groupChats;
	}

	public void deleteMessagesByID(Long messageID) {
		try {
			openDB();
			database.delete(TABLE_MESSAGES, COLUMN_MESSAGE_ID + " = ?",
					new String[] { Long.toString(messageID) });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteAllVideoCallMessagesByTargetID(Integer fromUserID) {
		List<MessageContent> messages = getMessages(
				Integer.toString(fromUserID), 0, 0);
		for (MessageContent messageContent : messages) {
			if (messageContent.getMsgType() == MsgType.RECEIVE_CHAT_VIDEO_REQUEST) {
				deleteMessagesByID(messageContent.getMessageID());
			}
		}
	}

	public MessageContent getMessageContentByID(Long messageID) {
		MessageContent item = null;
		try {
			openDB();
			Cursor cursor = database.query(TABLE_MESSAGES, new String[] {
					COLUMN_MESSAGE_TARGET_ID, COLUMN_MESSAGE_CONTENT,
					COLUMN_MESSAGE_TARGET_NAME, COLUMN_MESSAGE_ID },
					COLUMN_MESSAGE_ID + "=?",
					new String[] { String.valueOf(messageID) }, null, null,
					null, null);
			if (cursor.getCount() > 0) {
				cursor.moveToFirst();
				item = convertCursorToMessageContent(cursor);
			}
			cursor.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return item;
	}

	public void updateMessageContent(MessageContent messageContent) {
		if (messageContent == null) {
			return;
		}
		try {
			openDB();
			ContentValues result = new ContentValues();
			result.put(COLUMN_MESSAGE_CONTENT, messageContent.toJson());
			database.update(
					TABLE_MESSAGES,
					result,
					COLUMN_MESSAGE_ID + " = ?",
					new String[] { String.valueOf(messageContent.getMessageID()) });
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void updateMessageState(Long messageID, MessageState messageState) {
		MessageContent messageContent = getMessageContentByID(messageID);
		if(messageContent == null){
			return;
		}
		messageContent.setMessageState(messageState);
		updateMessageContent(messageContent);
	}
}
